At 11:23 PM -0400 5/30/03, Tom Lane wrote:
>Peter Bierman <bierman@apple.com> writes:
>> At 10:29 PM -0400 5/30/03, Tom Lane wrote:
>>> Hm, why is that shown as a "filter" and not an "index condition"? And
>>> why is there an explicit conversion to timestamp with time zone in
>>> there? Better tell us about the exact data types involved here ...
>
>> I was hoping you'd say 'hm'. :-)
>
>> CREATE TABLE events (
>> "time" timestamp without time zone DEFAULT
>> ('now'::text)::timestamp(6) with time zone NOT NULL,
>
>Right. You're getting bit by ye same olde problem of datatype mismatch:
>the planner does not realize that there is any connection between the
>types "timestamp without time zone" and "timestamp with time zone", so
>the presence of a WHERE condition expressed in terms of a timestamp-with-
>tz operator doesn't induce it to do anything that a timestamp-without-tz
>index could recognize.
>
>Short answer is you probably ought to declare events.time as timestamp
>with time zone; or if you have a *really good* reason why it should not
>be declared that way, you ought to cast what you are comparing it to
>to timestamp without tz.
Ok, two more questions then:
1) is there an easy way for me to change the type in place?
2) I created the table initially with:
CREATE TABLE events
(
time TIMESTAMP UNIQUE NOT NULL DEFAULT CURRENT_TIMESTAMP,
Should the plain 'timestamp' type really default to a different type
than what all the rest of the timestamp operators prefer?
Thanks!
-pmb
